﻿Imports DTO
Imports System.Data.OleDb
Public Class KhachHangDAO
    Public Shared Function DangKyKhachHang(ByVal KhachHang As BangKhachHangDTO) As Integer

        Dim cmd As OleDbCommand = New OleDbCommand()
        cmd.Connection = DataProvider.ConnectDB()
        cmd.CommandText = "insert into KHACHHANG(TenKhachHang,DiaChi,DienThoai,Email,SoTienNo) values(@TenKhachHang,@DiaChi,@DienThoai,Email,@SoTienNo)"
        Dim para As OleDbParameter = cmd.Parameters.Add("@TenKhachHang", OleDbType.WChar)
        para.Value = KhachHang.strTenKhachHang
        para = cmd.Parameters.Add("@DiaChi", OleDbType.WChar)
        para.Value = KhachHang.strDiaChi

        para = cmd.Parameters.Add("@DienThoai", OleDbType.WChar)
        para.Value = KhachHang.DDienThoai
        para = cmd.Parameters.Add("@Email", OleDbType.WChar)
        para.Value = KhachHang.strEmail
        para = cmd.Parameters.Add("@soTienNo", OleDbType.Double)
        para.Value = KhachHang.dSoTienNo

        cmd.ExecuteNonQuery()
        cmd.CommandText = "select @@IDENTITY"
        Return cmd.ExecuteScalar()
    End Function
    ' lay so tien no cua khach hang nay
    Public Shared Function LaySoTienKhachHangNo(ByVal KhachHangDTO As BangKhachHangDTO) As Double
        '  Try


        Dim kq As Double
        Dim connect As OleDbConnection = DataProvider.ConnectDB()
        Dim sql As String = "select SoTienNo from KHACHHANG where TenKhachHang =? and DiaChi=? and DienThoai=? and Email =?"
        Dim cmd As OleDbCommand = New OleDbCommand(sql, connect)
        cmd.Parameters.Add("@TenKhachHang", OleDbType.WChar)
        cmd.Parameters("@TenKhachHang").Value = KhachHangDTO.strTenKhachHang

        cmd.Parameters.Add("@DiaChi", OleDbType.WChar)
        cmd.Parameters("@DiaChi").Value = KhachHangDTO.strDiaChi

        cmd.Parameters.Add("@DienThoai", OleDbType.Double)
        cmd.Parameters("@DienThoai").Value = KhachHangDTO.DDienThoai

        cmd.Parameters.Add("@Email", OleDbType.WChar)
        cmd.Parameters("@Email").Value = KhachHangDTO.strEmail
        kq = cmd.ExecuteScalar()
        connect.Close()
        Return kq
        'Catch ex As Exception
        '    Return -1
        'End Try

    End Function
    ' Cap nhat lai so tien no sau khi khach hang da thanh toan
    Public Shared Function CapNhatLaiTienNo(ByVal KhachHangDTO As BangKhachHangDTO) As Double
        Dim kq As Double
        Dim connect As OleDbConnection = DataProvider.ConnectDB()
        Dim sql As String = "Update KHACHHANG set SoTienNo=? where TenKhachHang = ? and DiaChi =? and DienThoai =? and Email= ?  "
        Dim cmd As OleDbCommand = New OleDbCommand(sql, connect)
        cmd.Parameters.Add("@SoTienNo", OleDbType.Double)
        cmd.Parameters("@SoTienNo").Value = KhachHangDTO.dSoTienNo

        cmd.Parameters.Add("@TenKhachHang", OleDbType.WChar)
        cmd.Parameters("@TenKhachHang").Value = KhachHangDTO.strTenKhachHang

        cmd.Parameters.Add("@DiaChi", OleDbType.WChar)
        cmd.Parameters("@DiaChi").Value = KhachHangDTO.strDiaChi

        cmd.Parameters.Add("@DienThoai", OleDbType.Double)
        cmd.Parameters("@DienThoai").Value = KhachHangDTO.DDienThoai

        cmd.Parameters.Add("@Email", OleDbType.WChar)
        cmd.Parameters("@Email").Value = KhachHangDTO.strEmail
        kq = cmd.ExecuteNonQuery()
        Return kq
    End Function

    Public Shared Function LayToanBoKhachHang() As DataTable
        Dim dt As DataTable = New DataTable()
        Dim cnn As OleDbConnection = DataProvider.ConnectDB()
        Dim bui As OleDbDataAdapter = New OleDbDataAdapter("select * from KHACHHANG  ", cnn)
        bui.Fill(dt)
        cnn.Close()
        Return dt
    End Function
    Public Shared Function LayNhungKhachHangConBiNo() As DataTable
        Dim dt As DataTable = New DataTable()
        Dim cnn As OleDbConnection = DataProvider.ConnectDB()
        Dim bui As OleDbDataAdapter = New OleDbDataAdapter("select * from KHACHHANG where SoTienNo<> 0 ", cnn)
        bui.Fill(dt)
        cnn.Close()
        Return dt
    End Function

    Public Shared Function DoDuLieuVaoPhieuThu(ByVal MKHang As Integer)
       
        Dim dt As DataTable = New DataTable()

        Dim cnn As OleDbConnection = DataProvider.ConnectDB()
        Dim bui As OleDbDataAdapter = New OleDbDataAdapter("select * from KHACHHANG where MaKhachHang=" & MKHang & " order by TenKhachHang ", cnn)
        bui.Fill(dt)
        cnn.Close()
        Return dt
    End Function
    Public Shared Function KiemTraTrung(ByVal KhachHang As BangKhachHangDTO) As Integer
        Dim connect As OleDbConnection = DataProvider.ConnectDB()
        Dim sql As String = "select count(*)  From KHACHHANG where TenKhachHang = ? and DiaChi =? and DienThoai =? and Email= ?  "
        Dim cmd As OleDbCommand = New OleDbCommand(sql, connect)


        cmd.Parameters.Add("@TenKhachHang", OleDbType.WChar)
        cmd.Parameters("@TenKhachHang").Value = KhachHang.strTenKhachHang

        cmd.Parameters.Add("@DiaChi", OleDbType.WChar)
        cmd.Parameters("@DiaChi").Value = KhachHang.strDiaChi

        cmd.Parameters.Add("@DienThoai", OleDbType.Double)
        cmd.Parameters("@DienThoai").Value = KhachHang.DDienThoai

        cmd.Parameters.Add("@Email", OleDbType.WChar)
        cmd.Parameters("@Email").Value = KhachHang.strEmail

        Dim kq As Integer = cmd.ExecuteScalar()
        Return kq

    End Function

    Public Shared Function LayMaKHachHang(ByVal KhachHang As BangKhachHangDTO) As Integer
        Dim connect As OleDbConnection = DataProvider.ConnectDB()
        Dim sql As String = "select MaKHachHang   From KHACHHANG where TenKhachHang = ? and DiaChi =? and DienThoai =? and Email= ?  "
        Dim cmd As OleDbCommand = New OleDbCommand(sql, connect)


        cmd.Parameters.Add("@TenKhachHang", OleDbType.WChar)
        cmd.Parameters("@TenKhachHang").Value = KhachHang.strTenKhachHang

        cmd.Parameters.Add("@DiaChi", OleDbType.WChar)
        cmd.Parameters("@DiaChi").Value = KhachHang.strDiaChi

        cmd.Parameters.Add("@DienThoai", OleDbType.Double)
        cmd.Parameters("@DienThoai").Value = KhachHang.DDienThoai

        cmd.Parameters.Add("@Email", OleDbType.WChar)
        cmd.Parameters("@Email").Value = KhachHang.strEmail

        Dim kq As Integer = cmd.ExecuteScalar()
        Return kq

    End Function

  
End Class

